﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Microsoft.ApplicationBlocks.Data;

namespace TIS.DAO
{
    public class SettingDAO
    {
        private static readonly String Url = ConfigurationManager.AppSettings["Main.ConnectionString"];
        public SettingDAO()
        {

        }

        private SqlConnection _connection;
        private SqlConnection GetConnection()
        {

            _connection = new SqlConnection(Url);
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            return _connection;
        }


        public DataTable GetInfo(int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 1 * FROM Settings WHERE SettingKey = 'Info' AND LanguageId = @LanguageId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }
        public String GetValue(string key,int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@SettingKey", key);
                spr[1] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Settings WHERE SettingKey = @SettingKey AND LanguageId = @LanguageId", spr);
                return ds.Tables[0].Rows.Count > 0 ? ds.Tables[0].Rows[0]["SettingValue"].ToString() : null;
            }
            finally
            {
                _connection.Close();
            }
        }
        public DataTable GetSetting(int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Settings WHERE LanguageId = @LanguageId",spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public void UpdateSetting(string settingKey,string settingValue,int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[3];
                spr[0] = new SqlParameter("@SettingKey", settingKey);
                spr[1] = new SqlParameter("@SettingValue", settingValue);
                spr[2] = new SqlParameter("@LanguageId", langId);
                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text, "UPDATE Settings SET SettingValue = @SettingValue WHERE SettingKey = @SettingKey AND LanguageId = @LanguageId", spr);
            }
            finally
            {
                _connection.Close();
            }
        }
    }
}